//////////////////////////////////////////////////////////////////////////////////
//                                                                              //
//  This file is part of the buola project (https://code.google.com/p/buola/).  //
//                                                                              //
//  Copyright(c) 2007-2012 Xavi Gratal                                          //
//  gratal AT gmail DOT com                                                     //
//                                                                              //
//  Buola is free software: you can redistribute it and/or modify               //
//  it under the terms of the GNU General Public License as published by        //
//  the Free Software Foundation, either version 3 of the License, or           //
//  (at your option) any later version.                                         //
//                                                                              //
//  Buola is distributed in the hope that it will be useful,                    //
//  but WITHOUT ANY WARRANTY; without even the implied warranty of              //
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the               //
//  GNU General Public License for more details.                                //
//                                                                              //
//  You should have received a copy of the GNU General Public License           //
//  along with buola.  If not, see <http://www.gnu.org/licenses/>.              //
//                                                                              //
//////////////////////////////////////////////////////////////////////////////////

#ifndef _SQL_MEDIA_H_
#define _SQL_MEDIA_H_

namespace sql_media {

static const char sPragmas[]=
        "PRAGMA synchronous=0;"
        "PRAGMA count_changed=0;"
        "PRAGMA cache_size=5000;"
        "PRAGMA auto_vacuum=0;"
        "PRAGMA temp_store=MEMORY;"
        "PRAGMA legacy_file_format=OFF;"
        "PRAGMA foreign_keys=ON;"
        "PRAGMA encoding=\"UTF-8\";";

static const char sCreateTables[]=
        "CREATE TABLE Folders"
        "("
        "   FolderID          INTEGER PRIMARY KEY NOT NULL,"
        "   URL               TEXT NOT NULL,"
        "   "
        "   UNIQUE (URL)"
        ");"

        "CREATE TABLE Items"
        "("
        "   ItemID            INTEGER PRIMARY KEY NOT NULL,"
        "   URL               TEXT NOT NULL,"
        "   Folder            INTEGER NOT NULL,"
        "   MimeType          TEXT NOT NULL,"
        "   "
        "   UNIQUE (URL),"
        "   FOREIGN KEY(Folder) REFERENCES Folders(FolderID)"
        ");"

        "CREATE TABLE ItemsData"
        "("
        "   Item              INTEGER NOT NULL,"
        "   DataKey           TEXT NOT NULL,"
        "   DataValue         TEXT NOT NULL,"
        "   "
        "   FOREIGN KEY(Item) REFERENCES Items(ItemID)"
        ");"

        "CREATE TABLE LabelTypes"
        "("
        "   TypeID            INTEGER PRIMARY KEY NOT NULL,"
        "   Name              TEXT NOT NULL,"
        "   "
        "   UNIQUE (Name)"
        ");"

        "CREATE TABLE Labels"
        "("
        "   LabelID           INTEGER PRIMARY KEY NOT NULL,"
        "   LabelType         INTEGER NOT NULL,"
        "   Name              TEXT NOT NULL,"
        "   "
        "   FOREIGN KEY(LabelType) REFERENCES LabelTypes(TypeID)"
        ");"

        "CREATE TABLE LabelsData"
        "("
        "   Label             INTEGER NOT NULL,"
        "   DataKey           TEXT NOT NULL,"
        "   DataValue         TEXT NOT NULL,"
        "   "
        "   FOREIGN KEY(Label) REFERENCES Labels(LabelID)"
        ");"

        "CREATE TABLE ItemLabels"
        "("
        "   Item              INTEGER NOT NULL,"
        "   Label             INTEGER NOT NULL,"
        "   "
        "   FOREIGN KEY(Item) REFERENCES Items(ItemID)"
        "   FOREIGN KEY(Label) REFERENCES Labels(LabelID)"
        ");"

        "CREATE TABLE RelationshipTypes"
        "("
        "   TypeID            INTEGER PRIMARY KEY NOT NULL,"
        "   Name              TEXT NOT NULL,"
        "   "
        "   UNIQUE (Name)"
        ");"

        "CREATE TABLE LabelRelationships"
        "("
        "   RelationshipType  INTEGER NOT NULL,"
        "   Label1            INTEGER NOT NULL,"
        "   Label2            INTEGER NOT NULL,"
        "   "
        "   FOREIGN KEY(RelationshipType) REFERENCES RelationshipTypes(TypeID)"
        "   FOREIGN KEY(Label1) REFERENCES Labels(LabelID)"
        "   FOREIGN KEY(Label2) REFERENCES Labels(LabelID)"
        ");";

static const char sDefaultData[]=
        "BEGIN TRANSACTION;"

        "INSERT INTO LabelTypes(Name) VALUES('Artist');"
        "INSERT INTO LabelTypes(Name) VALUES('Album');"
        "INSERT INTO LabelTypes(Name) VALUES('Genre');"

        "INSERT INTO RelationshipTypes(Name) VALUES('ArtistAlbum');"

        "END TRANSACTION;";

static const char sInsertFolder[]=
        "INSERT INTO Folders(URL) VALUES(?);";

static const char sInsertItem[]=
        "INSERT INTO Items(URL,Folder,MimeType) VALUES(?,?,?);";

static const char sInsertItemData[]=
        "INSERT INTO ItemsData(Item,DataKey,DataValue) VALUES(?,?,?);";

static const char sGetFolderID[]=
        "SELECT FolderID FROM Folders WHERE URL=?";

/*namespace sql_conf*/ }

#endif
